package com.bdy.lm.isu.dao;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

import com.bdy.lm.browser.domain.CarMeasure;
import com.bdy.lm.browser.domain.ParameterQuery;
import com.bdy.lm.browser.domain.StandardRoute;
import com.bdy.lm.isu.domain.ISUMessage;

public class ProcessISURequestDao {

	private QueryRunner qr = new TxQueryRunner();
	private Connection con = null;// 定义引用
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	public List<String> queryLoginMessage(ISUMessage isuMessage, String ISUFlag) throws SQLException {

		try {
			con = JdbcUtils.getConnection();
			String sql = "SELECT * FROM driver_tb WHERE ICID=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, isuMessage.getICID());
			rs = ps.executeQuery();
			List<String> loginMessageList = new ArrayList<String>();
			if (rs.next()) {
				loginMessageList.add(rs.getString("driverName"));// 0 driverName
				String companyId = rs.getString("companyId");
				ps.clearParameters();

				String queryLicense = "SELECT * FROM car_tb WHERE ISUFlag=?";
				ps = con.prepareStatement(queryLicense);
				ps.setString(1, ISUFlag);
				rs = ps.executeQuery();
				if (rs.next()) {
					loginMessageList.add(rs.getString("license"));// 1 license
				}
				ps.clearParameters();
				
				String queryCompany = "SELECT * FROM company_tb WHERE companyId=?";
				ps = con.prepareStatement(queryCompany);
				ps.setString(1, companyId);
				rs = ps.executeQuery();
				if (rs.next()) {
					loginMessageList.add(rs.getString("companyName"));// 2 companyName
					loginMessageList.add(rs.getString("areaId"));//3 areaId
					//System.out.println("areaId" + rs.getString("areaId"));
				}
			}
			return loginMessageList;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}

	public List<Long> queryLoginOutTime(String ICID, String state) throws SQLException {
		try {
			con = JdbcUtils.getConnection();
			String sql = "SELECT * FROM loginOut_tb WHERE ICID=? AND state=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, ICID);
			ps.setString(2, state);
			rs = ps.executeQuery();
			List<Long> timeList = new ArrayList<Long>();
			while (rs.next()) {
				// System.out.println("rs"+rs.getString("time"));
				timeList.add(Long.parseLong(rs.getString("time")));
			}
			return timeList;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}

	public boolean addISULoginOutMessage(ISUMessage isuLoginMessage, String ISUFlag) throws SQLException {
		try {
			con = JdbcUtils.getConnection();
			String sql = "insert into loginOut_tb values(?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, isuLoginMessage.getICID());
			ps.setString(2, ISUFlag);
			ps.setDouble(3, isuLoginMessage.getLatitude());
			ps.setDouble(4, isuLoginMessage.getLongitude());
			ps.setString(5, isuLoginMessage.getLoginOutTime());
			ps.setString(6, isuLoginMessage.getState());
			if(ps.executeUpdate() > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}

	public ISUMessage queryCommand(ISUMessage isuMessage, String ISUFlag) throws SQLException {
		
		try {
			con = JdbcUtils.getConnection();
			ISUMessage iMsg = isuMessage;
			String sql = "SELECT publishTime FROM command_tb WHERE ISUFlag=? AND isExecute='no'";
			ps = con.prepareStatement(sql);
			ps.setString(1, ISUFlag);
			rs = ps.executeQuery();
			List<Long> timeList = new ArrayList<Long>();
			while (rs.next()) {
				iMsg.setPublishTime(rs.getString("publishTime"));
				timeList.add(Long.parseLong(rs.getString("publishTime")));
			}
			ps.clearParameters();
			
			if (timeList.size() > 0) {
				String willSendCommandTime = Long.toString(Collections.min(timeList));				
				String sql1 = "SELECT * FROM command_tb WHERE ISUFlag=? AND publishTime=? AND isExecute='no'";
				ps = con.prepareStatement(sql1);
				ps.setString(1, ISUFlag);
				ps.setString(2, willSendCommandTime);
				rs = ps.executeQuery();
				while (rs.next()) {
					iMsg.setMessageID(rs.getInt("messageID"));
					iMsg.setMessage(rs.getString("msg"));
					//System.out.println(rs.getInt("messageID")+rs.getString("msg"));
				}
			}
			//System.out.println("msg"+iMsg.getMessage() + "id" + iMsg.getMessageID());
			return iMsg;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}
	
	public boolean changeExecute(ISUMessage isuMessage, String ISUFlag) throws SQLException {
		try {
			con = JdbcUtils.getConnection();
			String sql = "UPDATE command_tb SET isExecute='yes' WHERE ISUFlag=? AND publishTime=? AND isExecute='no'";
			ps = con.prepareStatement(sql);
			ps.setString(1, ISUFlag);
			ps.setString(2, isuMessage.getPublishTime());
			if (ps.executeUpdate() > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}
	
	public boolean addHeartBit(ISUMessage isuMessage, String ISUFlag) throws SQLException {
		
		try {
			con = JdbcUtils.getConnection();
			String sql = "SELECT ISUFlag FROM online_tb WHERE ISUFlag=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, ISUFlag);
			rs = ps.executeQuery();
			ps.clearParameters();
			
			String sql1 = "UPDATE online_tb SET reportTime=?, ICID=?, runState=? WHERE ISUFlag=?";
			String sql2 = "UPDATE online_tb SET reportTime=?, ICID=?, runState=?, ISUFlag=?";
			if(rs.next()) {
				ps = con.prepareStatement(sql1);
			} else {
				ps = con.prepareStatement(sql2);
			}
			ps.setString(1, isuMessage.getReportTime());
			ps.setString(2, isuMessage.getICID());
			//System.out.println(isuMessage.getRunState());
			if(isuMessage.getRunState().equals("working")) {
				ps.setString(3, "载客");
			} else if(isuMessage.getRunState().equals("none")) {
				ps.setString(3, "空驶");
			}
			ps.setString(4, ISUFlag);
			if (ps.executeUpdate() > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}

	/**
	 * 向数据库中添加交易信息
	 * @param i 终端上传的消息
	 * @param iSUFlag ISU标志
	 * @param locationTracks 实时位置监控信息Json数组
	 * @param isCheat 是否作弊
	 * @return 成功true，失败false
	 * @throws SQLException
	 * @throws IOException
	 */
	public boolean addTransaction(ISUMessage i, String iSUFlag, String locationTracks, String isCheat) throws SQLException,IOException {
		InputStream cMs = null;
		if (locationTracks != null) {
			cMs = new ByteArrayInputStream(locationTracks.getBytes());//将监控信息Json数组转化为输入流，作为blob对象存入数据库
		}
		try {
			String sql = "INSERT INTO transaction_tb VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = { iSUFlag, i.getICID(), i.getGetOnTime(), i.getGetOffTime(), i.getMoney(), i.getPrice(), 
								i.getDistance(),i.getSpendTime(), i.getNullRun(), i.getJourney(), i.getEvaluate(),
								i.getFuelSurcharge(), i.getWaitTime(), i.getTransitionType(), i.getCurrentRunTime(), cMs, isCheat};
			if (qr.update(sql, params) > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (cMs != null) {
				cMs.close();
			}
		}
	}
	
	public boolean queryTransaction(String ISUFlag, String getOnTime) throws SQLException {

		try {
			String sql = "SELECT * FROM transaction_tb WHERE ISUFlag=? AND getOnTime= ?";
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, ISUFlag);
			ps.setString(2, getOnTime);
			return ps.executeQuery().next();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}

	public boolean addLocation(ISUMessage i, String isu) {
		try {
			String sql = "INSERT INTO location_tb VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = {i.getLocationTime(), i.getLongitude(), i.getLatitude(), i.getSpeed(), i.getDirection(), i.getStatus_1(), 
					i.getStatus_2(), i.getStatus_3(), i.getStatus_4(), i.getAlarm_byte_1(), i.getAlarm_byte_2(), i.getAlarm_byte_3(),
					i.getAlarm_byte_4(), isu};
			if (qr.update(sql, params) > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
	public List<StandardRoute> queryStandardRoute () {
		try {
			String sql = "SELECT * FROM standardroute_tb";
			return qr.query(sql, new BeanListHandler<StandardRoute>(StandardRoute.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}	
	public boolean queryISUMeasureByLocationTracks(String locationTracks) throws SQLException{
		try {
			InputStream verfiyLocationTracks = new ByteArrayInputStream(locationTracks.getBytes());
			String sql = "SELECT * FROM scar_tb WHERE locationTracks=?";
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
			ps.setBinaryStream(1, verfiyLocationTracks, verfiyLocationTracks.available());
			return ps.executeQuery().next();
		} catch (SQLException | IOException e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}
	public boolean queryISUMeasureByVerifyTime(String verifyTime) throws SQLException{
		try {
			String sql = "SELECT * FROM scar_tb WHERE verifyTime=?";
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, verifyTime);
			return ps.executeQuery().next();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) con.close();
		}
	}
	public boolean addISUMeasure(CarMeasure carMeasure, String isuFlag, String license, String locationTracks, String verifyTime) throws IOException{
		InputStream verfiyLocationTracks = new ByteArrayInputStream(locationTracks.getBytes());//将监控信息Json数组转化为输入流，作为blob对象存入数据库
		try {
			String sql = "INSERT INTO scar_tb VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = {	isuFlag, license, 
								carMeasure.getAlongitude(), carMeasure.getAlatitude(), carMeasure.getAaltitude(), carMeasure.getAspeed(),
								carMeasure.getBlongtitde(), carMeasure.getBlatitude(), carMeasure.getBaltitude(), carMeasure.getBspeed(),
								carMeasure.getScarMeasure(), carMeasure.getIsRegular(), verfiyLocationTracks, verifyTime };
			return qr.update(sql, params) > 0;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			verfiyLocationTracks.close();
		}
	}
	
	public String queryLicense(String ISUFlag) {
		String sql = "SELECT license FROM car_tb WHERE ISUFlag=?";
		try {
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, ISUFlag);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getString("license");// 1 license
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	public boolean getParameterRepeat(String queryTime) {
		try {
			String sql = "SELECT * FROM taximeterparameter_tb WHERE queryTime=?";
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, queryTime);;
			return ps.executeQuery().next();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public boolean addISUParameter(ParameterQuery i) {
		try {
			String sql = "INSERT INTO taximeterparameter_tb VALUES(?,?,?,?,?)";
			Object[] params = {i.getISUFlag(), i.getLicense(), i.getParameter(), i.getTaximeterTime(), i.getQueryTime() };
			return qr.update(sql, params) > 0;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
